------------------------------------------------------------
-- @name: tuning_by_cardinality_feeback
-- @author: dion cho
-- @description: test for tuning by cardinality feedback
------------------------------------------------------------

-- create object
drop table t1 purge;
drop table t2 purge;

create table t1(id1 int, status1 char(5));
create table t2(id2 int);

create index t1_idx on t1(id1);
create index t2_idx on t2(id2);

-- generate data
insert into t1
select
    -- column id1 is uniform
    mod(r,1000),
    -- column status1 is skewed!!!
    case when r between 1 and 9000 
          then '-1' 
    else to_char(mod(r,1000)) end
from
    (select level as r from dual connect by level <= 10000)
order by dbms_random.random;

insert into t2
select
    mod(r,100)
from
    (select level as r from dual connect by level <= 10000);

commit;


-- gather statistics with "no" histogram
exec dbms_stats.gather_table_stats(user, 't1', cascade=>true, -
    estimate_percent=>100, method_opt=>'for all columns size 1', -
    no_invalidate=>false);

exec dbms_stats.gather_table_stats(user, 't2', cascade=>true, -
    estimate_percent=>100, method_opt=>'for all columns size 1', -
    no_invalidate=>false);


-- identify statistics
@tab_stat t1

@tab_stat t2


-- check cardinality feedback using gather_plan_statistics hint
-- where is the greatest cardinality discrepancy?
select /*+ gather_plan_statistics */
    count(*)
from
    t1 t1, t2 t2
where
    t1.id1 = t2.id2 and
    t1.id1 between 1 and 100 and
    t1.status1 = '-1'
;

@stat


-----------------------------------------------------------------------
-- solution1: create histogram
exec dbms_stats.gather_table_stats(user, 't1', estimate_percent=>100, -
    cascade=>true, method_opt=>'for columns status1 size skewonly', -
    no_invalidate=>false);
   
-- check histogram
@tab_stat t1

select /*+ gather_plan_statistics */
    count(*)
from
    t1 t1, t2 t2
where
    t1.id1 = t2.id2 and
    t1.id1 between 1 and 100 and
    t1.status1 = '-1'
;

@stat

----------------------------------------------------------------------
-- Solution2: Adjust density
exec dbms_stats.gather_table_stats(user, 't1', cascade=>true, -
    estimate_percent=>100, method_opt=>'for all columns size 1', -
    no_invalidate=>false);
   
exec dbms_stats.set_column_stats(user, 't1', 'status1', -
    density=>1, no_invalidate=>false);

select /*+ gather_plan_statistics */
    count(*)
from
    t1 t1, t2 t2
where
    t1.id1 = t2.id2 and
    t1.id1 between 1 and 100 and
    t1.status1 = '-1'
;

@stat

